﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace FifthSpaceDAL
{
     public static class SqlHelper
    {
         public static readonly string ConnString = "Data Source=(local);Initial Catalog=WineShop;Persist Security Info=True;User ID=sa;Password=211www112";
        #region ExecuteNonQuery 执行Sql语句或者存储过程后，返回影响的行数
         /// <summary>
         /// 
         /// </summary>
         /// <param name="connectionString"></param>
         /// <param name="commandType"></param>
         /// <param name="commandText"></param>
         /// <param name="commandParameters"></param>
         /// <returns></returns>
         public static int ExecuteNonQuery(string connectionString,CommandType commandType,string commandText,params SqlParameter[] commandParameters)
         {
             SqlCommand cmd = new SqlCommand();
             using (SqlConnection conn=new SqlConnection(connectionString))
             {
                 PrepareCommand(cmd, commandType, conn, commandText, commandParameters);
                 int val = cmd.ExecuteNonQuery();
                 return val;
             }
             

         }
         public static int ExecuteNonQuery(string connectionString, string spName, params object[] commandParameters)
         {
             SqlCommand cmd = new SqlCommand();
             using (SqlConnection conn = new SqlConnection(connectionString))
             {
                 PrepareCommand(cmd,  conn, spName, commandParameters);
                 int val = cmd.ExecuteNonQuery();
                 return val;
             }
         }
        #endregion
        #region ExecuteScalar 执行Sql语句或者存储过程后，返回第一行第一列，如插入新记录，可以返回ID
         /// <summary>
         /// 
         /// </summary>
         /// <param name="connectionString"></param>
         /// <param name="commandType"></param>
         /// <param name="commandText"></param>
         /// <param name="commandParameters"></param>
         /// <returns></returns>
         public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
         {
             SqlCommand cmd = new SqlCommand();
             using (SqlConnection conn = new SqlConnection(connectionString))
             {
                 PrepareCommand(cmd, commandType, conn, commandText, commandParameters);
                 object val = cmd.ExecuteScalar();
                 return val;
             }
         }
         /// <summary>
         /// 
         /// </summary>
         /// <param name="connectionString"></param>
         /// <param name="spName"></param>
         /// <param name="ParametersValues"></param>
         /// <returns></returns>
         public static object ExecuteScalar(string connectionString, string spName, params SqlParameter[] ParametersValues)
         {
             SqlCommand cmd = new SqlCommand();
             using (SqlConnection conn = new SqlConnection(connectionString))
             {
                 PrepareCommand(cmd, conn, spName, ParametersValues);
                 object val = cmd.ExecuteScalar();
                 return val;
             }
         }
        #endregion
        #region ExecuteReader 执行Sql语句或者存储过程后，返回一个DataReader
         /// <summary>
         /// 
         /// </summary>
         /// <param name="connectionString"></param>
         /// <param name="commandType"></param>
         /// <param name="commandText"></param>
         /// <param name="commandParameters"></param>
         /// <returns></returns>
         public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
         {
             SqlConnection conn = new SqlConnection(connectionString);

             try
             {
                 SqlCommand cmd = new SqlCommand();
                 PrepareCommand(cmd, commandType, conn, commandText, commandParameters);
                 SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                 return rdr;
             }
             catch (Exception)
             {
                 conn.Close();
                 throw;
             }

         }
         public static SqlDataReader ExecuteReader(string connectionString, string spName, params SqlParameter[] ParametersValues)
         {
             SqlConnection conn = new SqlConnection(connectionString);

             try
             {
                 SqlCommand cmd = new SqlCommand();
                 PrepareCommand(cmd,  conn, spName, ParametersValues);
                 SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                 return rdr;
             }
             catch (Exception)
             {
                 conn.Close();
                 throw;
             }

         }
        
        #endregion
        #region ExecuteDataset 执行Sql语句或者存储过程后，返回一个DataSet
         public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
         {
             SqlCommand cmd = new SqlCommand();
             using (SqlConnection conn = new SqlConnection(connectionString))
             {
                 PrepareCommand(cmd, commandType, conn, commandText, commandParameters);
                 using (SqlDataAdapter da=new SqlDataAdapter(cmd))
                 {
                     DataSet ds = new DataSet();
                     da.Fill(ds);
                     return ds;
                 }
             }
         }
         public static DataSet ExecuteDataset(string connectionString,  string spName, params SqlParameter[] ParametersValues)
         {
             SqlCommand cmd = new SqlCommand();
             using (SqlConnection conn = new SqlConnection(connectionString))
             {
                 PrepareCommand(cmd, conn, spName, ParametersValues);
                 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                 {
                     DataSet ds = new DataSet();
                     da.Fill(ds);
                     return ds;
                 }
             }
         }
        #endregion
        #region PrepareCommand构建一个Command对象类供内部方法调用 
         /// <summary>
         /// 设置一个等待执行的SqlCommand对象
         /// </summary>
         /// <param name="cmd">SqlCommand对象，不允许为空对象</param>
         /// <param name="commandType">命令类型</param>
         /// <param name="conn">SqlConnectioin对象，不允许为空对象</param>
         /// <param name="commandText">sql语句</param>
         /// <param name="cmdParms">SqlParameters参数数组，允许为空对象</param>
         private static void PrepareCommand(SqlCommand cmd,CommandType commandType,SqlConnection conn,string commandText,SqlParameter[] cmdParms)
         { 
             //打开连接
             if (conn.State!=ConnectionState.Open)
             {
                 conn.Open();
             }
             //设置sqlCommand对象
             cmd.Connection = conn;
             cmd.CommandText = commandText;
             cmd.CommandType = commandType;
             if (cmdParms!=null)
             {
                 foreach (SqlParameter parm in cmdParms)
                 {
                     cmd.Parameters.Add(parm);
                 }
             }

         }
         /// <summary>
         /// 
         /// </summary>
         /// <param name="cmd"></param>
         /// <param name="conn"></param>
         /// <param name="spName"></param>
         /// <param name="parameterValues"></param>
         private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, string spName, params object[] parameterValues)
         {
             //打开连接
             if (conn.State != ConnectionState.Open)
             {
                 conn.Open();
             }
             //设置sqlCommand对象
             cmd.Connection = conn;
             cmd.CommandText = spName;
             cmd.Parameters.Clear();
             if (parameterValues != null)
             {
                 //方法一
                 //foreach (SqlParameter parm in parameterValues)
                 //{
                 //    cmd.Parameters.Add(parm);
                 //}
                 //方法二
                 for (int i = 0; i < cmd.Parameters.Count; i++)
                 {
                     cmd.Parameters[i].Value = parameterValues[i];
                 }
             }

         }
        #endregion

    }
}
